--    Program: 	  CAPCMIMP_CE_STATEMENT_RECONCILS_ALL.sql
--    Description:  Import CM STATEMENT RECONCILIATION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   ANDY MAK
--    Date:         8-MAY-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg		      varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_stmt_line_id_new		number;
                    		                 		
   cursor cur_1 is
      select * from CRST_CE_STMT_RECONCILS_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_1 loop   	
     l_err_msg_tmp := '';
     
     l_stmt_line_id_new := null;

     begin
       SELECT STATEMENT_LINE_ID_NEW
       INTO   l_stmt_line_id_new
       FROM   CRST_CE_STATEMENT_LINES
       WHERE  statement_line_id = cur.statement_line_id;

     exception
       when others then
         l_err_msg_tmp := 'Statement Line ID : '||cur.statement_line_id || ' not found';
     end;

   if l_stmt_line_id_new is not null then
     begin
       -- import data
       insert into ce_statement_reconcils_all
       (
  		STATEMENT_LINE_ID       ,
  		REFERENCE_TYPE          ,
  		REFERENCE_ID            ,
  		CREATED_BY              ,
  		CREATION_DATE           ,
  		LAST_UPDATED_BY         ,
  		LAST_UPDATE_DATE        ,
  		JE_HEADER_ID            ,
  		ORG_ID                  ,
  		REFERENCE_STATUS        ,
  		STATUS_FLAG             ,
  		ACTION_FLAG             ,
  		CURRENT_RECORD_FLAG     ,
  		AUTO_RECONCILED_FLAG    ,
  		AMOUNT                  ,
  		REQUEST_ID              ,
  		PROGRAM_APPLICATION_ID  ,
  		PROGRAM_ID              ,
  		PROGRAM_UPDATE_DATE     
       )
       values
       (
  		l_stmt_line_id_new      ,
  		cur.REFERENCE_TYPE          ,
  		cur.REFERENCE_ID            ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
  		cur.CREATION_DATE           ,
  		CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
  		cur.LAST_UPDATE_DATE        ,
  		cur.JE_HEADER_ID            ,
  		CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORG_ID),
  		cur.REFERENCE_STATUS        ,
  		cur.STATUS_FLAG             ,
  		cur.ACTION_FLAG             ,
  		cur.CURRENT_RECORD_FLAG     ,
  		cur.AUTO_RECONCILED_FLAG    ,
  		cur.AMOUNT                  ,
  		cur.REQUEST_ID              ,
  		cur.PROGRAM_APPLICATION_ID  ,
  		cur.PROGRAM_ID              ,
  		cur.PROGRAM_UPDATE_DATE     
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
   end if;
 
     if (l_err_msg_tmp = '' or l_err_msg_tmp is null) then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   if l_err_msg is null then
     l_err_msg := 'No Error';
   end if;

   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPCM_STATEMENT_RECONCILS_ALL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
